﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using FixtureManageSystem.Views;
using System.Data.SQLite;
using Google.Protobuf;
using System.Globalization;
using System.Collections.ObjectModel;

namespace FixtureManageSystem.Models
{
    public class WareHouseInService
    {
        public SqliteHelper sqliteHelper = new SqliteHelper();
        public WareHouseInService()
        {

        }

        // 获取入库表的总数。应该要加上条件吧, 
        public int GetWareHouseInsCount()
        {
            // 创建一变量来存储返回值
            int count;
            // 创建要执行的Sql 语句
            string str = "SELECT COUNT(*) as totalNumber FROM WareHouseIn";
            // 2、执行SQL语句并接受结果
            SQLiteDataReader Sdr = sqliteHelper.read(str);

            // 3、读取结果的数据
            while (Sdr.Read())
            {
                count = Convert.ToInt32(Sdr["totalNumber"]);
                return count;
            }
            return 0;
        }

        /// <summary>
        /// 查询第 page 页，pagesize条入库表目，其实仅需改变列表_whis就行
        /// </summary>
        public ObservableCollection<WareHouseIn> InquireWareHouseIn(int page, int pageSize)
        {

            ObservableCollection<WareHouseIn> result = new ObservableCollection<WareHouseIn>();

            // 创建要执行的Sql语句， 这里需要联合两张表查询了
            string str = "select WareHouseIn.* , EmPloyee.Name as PersonName from WareHouseIn "
            + "Join Employee on WareHouseIn.PersonNo = Employee.PersonNo "
            + $" order by Id DESC  LiMIT {pageSize} OFFSET {(page - 1) * pageSize} ;";

            // 2、执行SQL语句并接受结果
            SQLiteDataReader Sdr = sqliteHelper.read(str);

            // 4、读取结果的数据
            while (Sdr.Read())
            {
                result.Add(new WareHouseIn()
                {
                    
                    Id = Convert.ToInt32(Sdr["Id"]),
                    BarCode = Sdr["BarCode"].ToString(), // 条形码
                    Name = Sdr["Name"].ToString(), // 夹具名称
                    MaterialType = Sdr["MaterialType"].ToString(), // 物料类型
                    Num = Convert.ToInt32(Sdr["Num"]), // 入库数量
                    Position = Sdr["Position"].ToString(),
                    PersonNo = Sdr["PersonNo"].ToString(), // 责任人工号
                    PersonName = Sdr["PersonName"].ToString(),  // 责任人姓名
                    InTime = DateTime.Parse(Sdr["InTime"].ToString())
                });

            }

            return result;
        }



        public bool IsExists(string BarCode)
        {

            string str = $"SELECT * FROM Fixture WHERE BarCode='{BarCode}'";
            // 打开数据库
            SQLiteDataReader Sdr = sqliteHelper.read(str);
            while (Sdr.Read())
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 新增出库单
        /// </summary>
        public bool Add(WareHouseIn WareHouseIn)
        {
            string str = "BEGIN;";
            if (IsExists(WareHouseIn.BarCode))
            {
                str += string.Format(" UPDATE Fixture SET Num = Num + {0} WHERE BarCode = '{1}'; ", WareHouseIn.Num, WareHouseIn.BarCode);
            }
            else
            {
                str += string.Format(
                    @"INSERT INTO Fixture(BarCode, Name, MaterialType, Num,Position) VALUES
                    ('{0}', '{1}', '{2}', '{3}', '{4}');", WareHouseIn.BarCode, WareHouseIn.Name, WareHouseIn.MaterialType, WareHouseIn.Num,WareHouseIn.Position);
            }
            // 创建要执行的Sql 语句
            str += String.Format(
                "\nINSERT INTO WareHouseIn(BarCode, PersonNo, Num, InTime, MaterialType, Name, Position) VALUES('{0}', '{1}', {2}, '{3}', '{4}','{5}','{6}' );\r\nCOMMIT;",
                WareHouseIn.BarCode, WareHouseIn.PersonNo, WareHouseIn.Num, WareHouseIn.InTime.ToString("yyyy-MM-dd"), WareHouseIn.MaterialType, WareHouseIn.Name, WareHouseIn.Position
             );
            // 3、执行SQL语句并接受结果
            int r = sqliteHelper.Execute(str);
            return r > 0;
        }

        public  bool Update(WareHouseIn wareHouseIn)
        {

            // 创建要执行的Sql 语句 不建议这里修改入库数量或者条码或者工号
            string str = "BEGIN; " +
                        $"UPDATE WareHouseIn SET Num = {wareHouseIn.Num}, PersonNo = '{wareHouseIn.PersonNo}', InTime = '{wareHouseIn.InTime.ToString("yyyy-MM-dd")}', " +
                        $"BarCode = '{wareHouseIn.BarCode}', MaterialType = '{wareHouseIn.MaterialType}', Name = '{wareHouseIn.Name}', Position = '{wareHouseIn.Position}' " +
                        $" WHERE id = {wareHouseIn.Id};" +
                         "COMMIT;";
            // 3、执行SQL语句并接受结果
            int r = sqliteHelper.Execute(str);
            return r > 0;
        }


        // 删除入库记录
        public bool Delete(WareHouseIn wareHouseIn)
        {
            // 创建要执行的Sql 语句，
            string str = $" BEGIN; " +
                       $"DELETE FROM WareHouseIn WHERE Id = '{wareHouseIn.Id}'; " +
                       $" COMMIT;";
            // 3、执行SQL语句并接受结果
            int r = sqliteHelper.Execute(str);
            return r > 0;
        }

        public WareHouseIn GetWareHouseIn(int Id)
        {
            // 创建要执行的Sql语句， 这里需要联合两张表查询了
            string str = "select WareHouseIn.* , EmPloyee.Name as PersonName from WareHouseIn "
            + "Join Employee on WareHouseIn.PersonNo = Employee.PersonNo "
            + $" Where WarehouseIn.Id = {Id}";
            WareHouseIn result = null;
            SQLiteDataReader Sdr = sqliteHelper.read(str);
            while (Sdr.Read())
            {
                result = new WareHouseIn()
                {
                    Id = Convert.ToInt32(Sdr["Id"]),
                    BarCode = Sdr["BarCode"].ToString(), // 条形码
                    Name = Sdr["Name"].ToString(), // 夹具名称
                    MaterialType = Sdr["MaterialType"].ToString(), // 物料类型
                    Num = Convert.ToInt32(Sdr["Num"]), // 入库数量
                    Position = Sdr["Position"].ToString(),
                    PersonNo = Sdr["PersonNo"].ToString(), // 责任人工号
                    PersonName = Sdr["PersonName"].ToString(),  // 责任人姓名
                    InTime = DateTime.Parse(Sdr["InTime"].ToString())
                };
            }
            return result;
        }
        /// <summary>
        /// 根据输入框填入的条形码、起止日期、入库人等，查询入库信息。这样写是不能防止sql注入的，
        /// </summary>
        public ObservableCollection<WareHouseIn> SearchWareHouseIn(WareHouseSearch em)
        {
            ObservableCollection <WareHouseIn> result = new ObservableCollection<WareHouseIn>();
            string personName = em.PersonName;
            string barcode = em.BarCode;
            string name = em.Name;

            DateTime startTime = em.StartTime;
            DateTime endTime = em.EndTime;

            // 创建要执行的Sql 语句
            string str = $"select WareHouseIn.*,  Employee.Name as PersonName from WareHouseIn Join Employee on  Employee.PersonNo = WareHouseIn.PersonNo Where ";
            List<string> conditions = new List<string>();
            if (!string.IsNullOrEmpty(personName))
            {
                conditions.Add($"PersonName Like '%{personName}%' ");
            }
            if (!string.IsNullOrEmpty(name))
            {
                conditions.Add($" WareHouseIn.Name Like '%{name}%' ");
            }
            if (!string.IsNullOrEmpty(barcode))
            {
                conditions.Add($" WareHouseIn.Barcode Like '%{barcode}%' ");
            }
            if (startTime != new DateTime(1, 1, 1))
            {
                conditions.Add($" WareHouseIn.InTime >= '{startTime.ToString("yyyy-MM-dd")}'");
            }

            if (endTime != new DateTime(1, 1, 1))
            {
                conditions.Add($" WareHouseIn.InTime <= '{endTime.ToString("yyyy-MM-dd")}'");
            }

            if (conditions.Count == 0)
            {
                return result;
            }
            str += string.Join(" AND ", conditions);

            SQLiteDataReader Sdr = sqliteHelper.read(str);

            int count = 0;
            // 4、读取结果的数据
            while (Sdr.Read())
            {

                result.Add(new WareHouseIn()
                {
                    BarCode = Sdr["BarCode"].ToString(),
                    PersonNo = Sdr["PersonNo"].ToString(),
                    Name = Sdr["Name"].ToString(),
                    PersonName = Sdr["PersonName"].ToString(),
                    MaterialType = Sdr["MaterialType"].ToString(),
                    Position = Sdr["Position"].ToString(),
                    InTime = Convert.ToDateTime(Sdr["Intime"]),
                    Num = Convert.ToInt32(Sdr["Num"]),
                    Id = Convert.ToInt32(Sdr["Id"])
                }); 
                count += 1;
            }
            return result;
        }
    }
}
